package com.oracle.lingshangejia.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.Map;
import java.util.Set;

public class HealthCheckSQLiteHelper extends SQLiteOpenHelper {
    private final static String DATABASE_NAME = "lingShangYiJia";
    private final static int DATABASE_VERSION = 1;
    private final static String TABLE_NAME = "HealthArchives";

    //构造函数，创建数据库
    public HealthCheckSQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // 建表
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + TABLE_NAME
                + "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + " g_id bigint(20)  NOT NULL,"// 居民Id
                + " tjbh varchar(100),"// 体检编号
                + " tjrq VARCHAR(100),"// 体检日期
                + " zrys VARCHAR(100),"// 责任医生
                + " zz VARCHAR(100),"// 症状
                + " tw int(5),"// 体温
                + " ml int(5),"// 脉率
                + " hxpl int(5),"// 呼吸频率
                + " zcxy int(5),"// 左侧血压
                + " ycxy int(5),"// 右侧血压
                + " sg int(5),"// 身高
                + " tz int(5),"//体重
                + " yw int(5),"// 	腰围
                + " tzzs int(5),"//体质指数
                + " jkztzwpg int(1),"//老年人健康状态自我评估
                + " zlnlzwpg int(1),"// 老年人生活自理能力自我评估
                + " rzgn int(1),"// 老年人认知功能
                + " qgzk int(1),"// 情感状况
                + " dlpl int(1),"// 锻炼频率
                + " mcdlsj int(5),"// 每次锻炼时间(分钟)
                + " jcdlsj int(5),"// 坚持锻炼时间(年)
                + " dlfs varchar(100),"//锻炼方式
                + " ysxg VARCHAR(100),"// 饮食习惯
                + " xyzk int(1),"// 吸烟状况
                + " rxyl int(5),"// 吸烟状况
                + " ksxynl int(5),"// 开始吸烟年龄
                + " jynl int(5),"//戒烟年龄
                + " yjpl int(1),"// 饮酒频率
                + " ryjl int(5),"// 日饮酒量
                + " sfjj int(1),"// 	是否戒酒
                + " jjnl int(5),"// 戒酒年龄
                + " ksyjnl int(5),"// 开始饮酒年龄
                + " jynsfyj int(1),"// 近一年是否醉酒
                + " yjzl varchar(100),"// 饮酒种类
                + " zybwh int(1),"// 职业病危害
                + " zybwh_gz varchar(100),"// 工种
                + " ss VARCHAR(100) DEFAULT '1',"// 手术，默认无
                + " ss_mc1 VARCHAR(100),"// 手术名称1
                + " ss_sj1 VARCHAR(100),"// 手术时间1
                + " ss_mc2 VARCHAR(100),"// 手术名称2
                + " ss_sj2 VARCHAR(100),"// 手术时间2
                + " ws VARCHAR(100) DEFAULT '1',"// 外伤，默认无
                + " ws_mc1 VARCHAR(100),"// 外伤名称1
                + " ws_sj1 VARCHAR(100),"// 外伤时间1
                + " ws_mc2 VARCHAR(100),"// 外伤名称2
                + " ws_sj2 VARCHAR(100),"// 外伤时间2
                + " sx VARCHAR(100) DEFAULT '1',"// 输血，默认无
                + " sx_yy1 VARCHAR(100),"// 输血原因1
                + " sx_sj1 VARCHAR(100),"// 输血时间1
                + " sx_yy2 VARCHAR(100),"// 输血原因2
                + " sx_sj2 VARCHAR(100),"// 输血时间2
                + " jzs_fq VARCHAR(100) DEFAULT '1',"// 家族史_父亲，默认无
                + " jzs_fq_qt VARCHAR(100),"// 家族史_父亲_其他
                + " jzs_mq VARCHAR(100) DEFAULT '1',"// 家族史母亲，默认无
                + " jzs_mq_qt VARCHAR(100),"// 家族史母亲其他
                + " jzs_xdjm VARCHAR(100) DEFAULT '1',"// 家族史兄弟姐妹，默认无
                + " jzs_xdjm_qt VARCHAR(100),"// 家族史兄弟姐妹其他
                + " jzs_zn VARCHAR(100) DEFAULT '1',"// 家族史子女，默认无
                + " jzs_zn_qt VARCHAR(100),"// 家族史子女其他
                + " ycbs VARCHAR(100) DEFAULT '1',"// 遗传病史，默认无
                + " ycbs_jbmc VARCHAR(100),"// 遗传病史 疾病名称
                + " cjqk VARCHAR(100) DEFAULT '1',"// 残疾情况，默认无残疾
                + " cjqk_qtcj VARCHAR(100),"// 残疾情况 其他残疾
                + " cfpfss VARCHAR(100) DEFAULT '1',"// 厨房排风设施，默认无
                + " rllx VARCHAR(100) DEFAULT '6',"// 燃料类型，默认其它
                + " ys VARCHAR(100) DEFAULT '6',"// 饮水，默认其它
                + " cs VARCHAR(100) DEFAULT '1',"// 厕所，默认卫生厕所
                + " qcl VARCHAR(100) DEFAULT '1')";// 禽畜栏，默认无

        String sql1="CREATE TABLE " + TABLE_NAME
                + "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
                +"g_Id bigint(20) DEFAULT NULL,"// COMMENT '个人信息id'
                +"tjbh varchar(100) DEFAULT NULL," // COMMENT '体检编号'
                +"tjrq date DEFAULT NULL," // COMMENT '体检日期',
                +"zrys bigint(20) DEFAULT NULL," // COMMENT '责任医生'
                +"zz varchar(200) DEFAULT NULL," // COMMENT '症状'
                +"tw int(5) DEFAULT NULL," // COMMENT '体温'
                +"ml int(5) DEFAULT NULL," // COMMENT '脉率'
                +"hxpl int(5) DEFAULT NULL," // COMMENT '呼吸频率'
                +"zcxy int(5) DEFAULT NULL," // COMMENT '左侧血压'
                + "ycxy int(5) DEFAULT NULL," // COMMENT '右侧血压'
                +"sg int(5) DEFAULT NULL," // COMMENT '身高'
                +"tz int(5) DEFAULT NULL," // COMMENT '体重'
                + "yw int(5) DEFAULT NULL," // COMMENT '腰围'
                +"tzzs int(5) DEFAULT NULL," // COMMENT '体质指数'
                +"jkztzwpg int(1) DEFAULT NULL," // COMMENT '老年人健康状态自我评估'
                + "zlnlzwpg int(1) DEFAULT NULL," // COMMENT '老年人生活自理能力自我评估'
                +"rzgn int(1) DEFAULT NULL," // COMMENT '老年人认知功能'
                + "qgzk int(1) DEFAULT NULL," // COMMENT '情感状况'
                + "dlpl int(1) DEFAULT NULL," // COMMENT '锻炼频率'
                +"mcdlsj int(5) DEFAULT NULL,"
                + "jcdlsj int(5) DEFAULT NULL,"
                +"dlfs varchar(100) DEFAULT NULL,"
                +"ysxg varchar(100) DEFAULT NULL,"
                +"xyzk int(1) DEFAULT NULL,"
                +"rxyl int(5) DEFAULT NULL,"
                +"ksxynl int(5) DEFAULT NULL,"
                +"jynl int(5) DEFAULT NULL,"
                +"yjpl int(1) DEFAULT NULL,"
                + "ryjl int(5) DEFAULT NULL,"
                +"sfjj int(1) DEFAULT NULL,"
                +"jjnl int(5) DEFAULT NULL,"
                +"ksyjnl int(5) DEFAULT NULL,"
                +"jynsfyj int(1) DEFAULT NULL,"
                +"yjzl varchar(100) DEFAULT NULL,"
                +"zybwh int(1) DEFAULT NULL,"
                + "zybwh_gz varchar(100) DEFAULT NULL,"
                +"zybwh_n int(5) DEFAULT NULL,"
                +"dwzl_fc varchar(100) DEFAULT NULL,"
                +"fc_fhcs int(1) DEFAULT NULL,"
                +"fc_fhcsms varchar(100) DEFAULT NULL,"
                +"dwzl_fswz varchar(100) DEFAULT NULL,"
                +"fswz_fhcs int(1) DEFAULT NULL,"
                +"fswz_fhcsms varchar(100) DEFAULT NULL,"
                +"dwzl_wlys varchar(100) DEFAULT NULL,"
                +"wlys_fhcs int(1) DEFAULT NULL,"
                +"wlys_fhcsms varchar(100) DEFAULT NULL,"
                +"dwzl_hxwz varchar(100) DEFAULT NULL,"
                +"hxwz_fhcs int(1) DEFAULT NULL,"
                +"hxwz_fhcsms varchar(100) DEFAULT NULL,"
                +"dwzl_qt varchar(100) DEFAULT NULL,"
                +"qt_fhcs int(1) DEFAULT NULL,"
                +"qt_fhcsms varchar(100) DEFAULT NULL,"
                +"kc int(1) DEFAULT NULL,"
                +"cl varchar(100) DEFAULT NULL,"
                +"qc1 varchar(100) DEFAULT NULL,"
                +"qc2 varchar(100) DEFAULT NULL,"
                +"qc3 varchar(100) DEFAULT NULL,"
                +"qc4 varchar(100) DEFAULT NULL,"
                +"quc1 varchar(100) DEFAULT NULL,"
                +"quc2 varchar(100) DEFAULT NULL,"
                +"quc3 varchar(100) DEFAULT NULL,"
                +"quc4 varchar(100) DEFAULT NULL,"
                +"yc1 varchar(100) DEFAULT NULL,"
                +"yc2 varchar(100) DEFAULT NULL,"
                +"yc3 varchar(100) DEFAULT NULL,"
                +"yc4 varchar(100) DEFAULT NULL,"
                +"yb int(1) DEFAULT NULL,"
                +"sl_zy double(10,2) DEFAULT NULL,"
                +"sl_yy double(10,2) DEFAULT NULL,"
                +"jzsl_zy double(10,2) DEFAULT NULL,"
                +"jzsl_yy double(10,2) DEFAULT NULL,"
                +"tl int(1) DEFAULT NULL,"
                +"ydgn int(1) DEFAULT NULL,"
                +"yd int(1) DEFAULT NULL,"
                +"pf int(1) DEFAULT NULL,"
                +"gm int(1) DEFAULT NULL,"
                +"lbj int(1) DEFAULT NULL,"
                +"tzx int(1) DEFAULT NULL,"
                +"hxy int(1) DEFAULT NULL,"
                +"ly int(1) DEFAULT NULL,"
                +"xlpc int(5) DEFAULT NULL,"
                +"xl int(1) DEFAULT NULL,"
                +"zy int(1) DEFAULT NULL,"
                +"fbyt int(1) DEFAULT NULL,"
                +"fbbk int(1) DEFAULT NULL,"
                +"fbgd int(1) DEFAULT NULL,"
                +"fbpd int(1) DEFAULT NULL,"
                +"fbydxzy int(1) DEFAULT NULL,"
                +"xzsz int(1) DEFAULT NULL,"
                +"zbdmbd int(1) DEFAULT NULL,"
                +"gmzz int(1) DEFAULT NULL,"
                +"rx varchar(100) DEFAULT NULL,"
                +"fk_wy int(1) DEFAULT NULL,"
                +"fk_wyms varchar(200) DEFAULT NULL,"
                +"fk_yd int(1) DEFAULT NULL,"
                +"fk_ydms varchar(200) DEFAULT NULL,"
                +"fk_gj int(1) DEFAULT NULL,"
                +"fk_gjms varchar(200) DEFAULT NULL,"
                +"fk_gt int(1) DEFAULT NULL,"
                +"fk_gtms varchar(200) DEFAULT NULL,"
                +"fk_fj int(1) DEFAULT NULL,"
                +"fk_fjms varchar(200) DEFAULT NULL,"
                +"fk_qt varchar(500) DEFAULT NULL,"
                +"xcg_xhdb double(10,2) DEFAULT NULL,"
                +"xcg_bxb double(10,2) DEFAULT NULL,"
                +"xcg_xxb double(10,2) DEFAULT NULL,"
                +"xcg_qt varchar(200) DEFAULT NULL,"
                +"ncg_ndb double(10,2) DEFAULT NULL,"
                +"ncg_nt double(10,2) DEFAULT NULL,"
                +"ncg_ntt double(10,2) DEFAULT NULL,"
                +"ncg_nqx double(10,2) DEFAULT NULL,"
                +"ncg_qt varchar(100) DEFAULT NULL,"
                +"kfxt1 double(10,2) DEFAULT NULL,"
                +"kfxt2 double(10,2) DEFAULT NULL,"
                +"xdt int(1) DEFAULT NULL,"
                +"nwlbdb double(10,2) DEFAULT NULL,"
                +"dbqx int(1) DEFAULT NULL,"
                +"thxhdb double(10,2) DEFAULT NULL,"
                +"ygky int(1) DEFAULT NULL,"
                +"gg_xqgbzam double(10,2) DEFAULT NULL,"
                +"gg_xqgczam double(10,2) DEFAULT NULL,"
                +"gg_bdb double(10,2) DEFAULT NULL,"
                +"gg_zdhs double(10,2) DEFAULT NULL,"
                +"gg_jhdhs double(10,2) DEFAULT NULL,"
                +"sg_xqjg double(10,2) DEFAULT NULL,"
                +"sg_xns double(10,2) DEFAULT NULL,"
                +"sg_xjnd double(10,2) DEFAULT NULL,"
                +"sg_xnnd double(10,2) DEFAULT NULL,"
                +"xz_zdgc double(10,2) DEFAULT NULL,"
                +"xz_gysz double(10,2) DEFAULT NULL,"
                +"xz_xqdmdzdbdgc double(10,2) DEFAULT NULL,"
                +"xz_xqgmdzdbdgc double(10,2) DEFAULT NULL,"
                +"xbxxp int(1) DEFAULT NULL,"
                +"xbxxp_yc varchar(50) DEFAULT NULL,"
                +"bc_fbbc int(1) DEFAULT NULL,"
                +"bc_fbbc_yc varchar(50) DEFAULT NULL,"
                +"bc_qt int(1) DEFAULT NULL,"
                +"bc_qt_yc varchar(50) DEFAULT NULL,"
                +"gjtp int(1) DEFAULT NULL,"
                +"gjtp_yc varchar(50) DEFAULT NULL,"
                +"fzjc_qt varchar(200) DEFAULT NULL,"
                +"nxgjb varchar(100) DEFAULT NULL,"
                +"nxgjb_qt varchar(100) DEFAULT NULL,"
                +"szjb varchar(100) DEFAULT NULL,"
                +"szjb_qt varchar(100) DEFAULT NULL,"
                +"xzjb varchar(100) DEFAULT NULL,"
                +"xzjb_qt varchar(100) DEFAULT NULL,"
                +"xgjb varchar(100) DEFAULT NULL,"
                +"xgjb_qt varchar(100) DEFAULT NULL,"
                +"ybjb varchar(100) DEFAULT NULL,"
                +"ybjb_qt varchar(100) DEFAULT NULL,"
                +"sjxtjb int(1) DEFAULT NULL,"
                +"sjxtjb_ms varchar(100) DEFAULT NULL,"
                +"qtxtjb int(1) DEFAULT NULL,"
                +"qtxtjb_ms varchar(100) DEFAULT NULL,"
                +"zys1_ryrq date DEFAULT NULL,"
                +"zys1_cyrq date DEFAULT NULL,"
                +"zys1_yy varchar(100) DEFAULT NULL,"
                +"zys1_yljgmc varchar(200) DEFAULT NULL,"
                +"zys1_bah varchar(100) DEFAULT NULL,"
                +"zys2_ryrq date DEFAULT NULL,"
                +"zys2_cyrq date DEFAULT NULL,"
                +"zys2_yy varchar(100) DEFAULT NULL,"
                +"zys2_yljgmc varchar(200) DEFAULT NULL,"
                +"zys2_bah varchar(100) DEFAULT NULL,"
                +"jtbcs1_jcrq date DEFAULT NULL,"
                +"jtbcs1_ccrq date DEFAULT NULL,"
                +"jtbcs1_yy varchar(100) DEFAULT NULL,"
                +"jtbcs1_yljgmc varchar(200) DEFAULT NULL,"
                +"jtbcs1_bah varchar(100) DEFAULT NULL,"
                +"jtbcs2_jcrq date DEFAULT NULL,"
                +"jtbcs2_ccrq date DEFAULT NULL,"
                +"jtbcs2_yy varchar(100) DEFAULT NULL,"
                +"jtbcs2_yljgmc varchar(200) DEFAULT NULL,"
                +"jtbcs2_bah varchar(100) DEFAULT NULL,"
                +"yyqk1_yymc varchar(200) DEFAULT NULL,"
                +"yyqk1_yf varchar(100) DEFAULT NULL,"
                +"yyqk1_yl varchar(100) DEFAULT NULL,"
                +"yyqk1_yysj varchar(100) DEFAULT NULL,"
                +"yyqk1_fyycx int(1) DEFAULT NULL,"
                +"yyqk2_yymc varchar(200) DEFAULT NULL,"
                +"yyqk2_yf varchar(100) DEFAULT NULL,"
                +"yyqk2_yl varchar(100) DEFAULT NULL,"
                +"yyqk2_yysj varchar(100) DEFAULT NULL,"
                +"yyqk2_fyycx int(1) DEFAULT NULL,"
                +"fmyghyfjzs1_mc varchar(100) DEFAULT NULL,"
                +"fmyghyfjzs1_jzrq date DEFAULT NULL,"
                +"fmyghyfjzs1_jzjg varchar(200) DEFAULT NULL,"
                +"fmyghyfjzs2_mc varchar(100) DEFAULT NULL,"
                +"fmyghyfjzs2_jzrq date DEFAULT NULL,"
                +"fmyghyfjzs2_jzjg varchar(200) DEFAULT NULL,"
                +"fmyghyfjzs3_mc varchar(100) DEFAULT NULL,"
                +"fmyghyfjzs3_jzrq date DEFAULT NULL,"
                +"fmyghyfjzs3_jzjg varchar(200) DEFAULT NULL,"
                +"jkpj int(1) DEFAULT NULL,"
                +"jkpj_yc1 varchar(200) DEFAULT NULL,"
                +"jkpj_yc2 varchar(200) DEFAULT NULL,"
                +"jkpj_yc3 varchar(200) DEFAULT NULL,"
                +"jkpj_yc4 varchar(200) DEFAULT NULL,"
                +"jkzd varchar(50) DEFAULT NULL,"
                +"wxyskz varchar(50) DEFAULT NULL,"
                +"wxyskz_jtz double(10,2) DEFAULT NULL,"
                +"wxyskz_jyjzym varchar(50) DEFAULT NULL,"
                +"wxyskz_qt varchar(200) DEFAULT NULL,"
                +"yyqk3_yymc varchar(200) DEFAULT NULL,"
                +"yyqk3_yf varchar(100) DEFAULT NULL,"
                +"yyqk3_yl varchar(100) DEFAULT NULL,"
                +"yyqk3_yysj varchar(100) DEFAULT NULL,"
                +"yyqk3_fyycx int(1) DEFAULT NULL,"
                +"yyqk4_yymc varchar(200) DEFAULT NULL,"
                +"yyqk4_yf varchar(100) DEFAULT NULL,"
                +"yyqk4_yl varchar(100) DEFAULT NULL,"
                +"yyqk4_yysj varchar(100) DEFAULT NULL,"
                +"yyqk4_fyycx int(1) DEFAULT NULL,"
                +"yyqk5_yymc varchar(200) DEFAULT NULL,"
                +"yyqk5_yf varchar(100) DEFAULT NULL,"
                +"yyqk5_yl varchar(100) DEFAULT NULL,"
                +"yyqk5_yysj varchar(100) DEFAULT NULL,"
                +"yyqk5_fyycx int(1) DEFAULT NULL,"
                +"yyqk6_yymc varchar(200) DEFAULT NULL,"
                +"yyqk6_yf varchar(100) DEFAULT NULL,"
                +"yyqk6_yl varchar(100) DEFAULT NULL,"
                +"yyqk6_yysj varchar(100) DEFAULT NULL,"
                +"yyqk6_fyycx int(1) DEFAULT NULL)";

        db.execSQL(sql1);
    }

    // 更新
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
        db.execSQL(sql);
        onCreate(db);
    }
    //获取游标
    public Cursor select() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
        return cursor;
    }

    //插入一条记录
    public long insert(Map<Object, Object> values) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        Set<Object> keys = values.keySet();
        for (Object key : keys) {
            cv.put((String)key,(String)values.get(key));
        }
        long row = db.insert(TABLE_NAME, null, cv);
        return row;
    }

    //更新记录
    public void update(Map<Object, Object> values, int idCard) {
        SQLiteDatabase db = this.getWritableDatabase();
        String where = "sfzh = ?";
        //String[] whereValue = {Integer.toString(id)};
        String[] whereValue = new String[]{idCard+""};
        ContentValues cv = new ContentValues();
        Set<Object> keys = values.keySet();
        for (Object key : keys) {
            // 根据身份证号更新数据
            if(!key.equals("sfzh")) {
                cv.put((String)key, (String)values.get(key));
            }
        }
        db.update(TABLE_NAME, cv, where, whereValue);
    }

    //根据身份证号查询
    public Cursor userName(String[] args) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE g_id = ?", args);
        return cursor;
    }
    //查询所有数据
    public Cursor queryAll() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME , null);
        return cursor;
    }

    public void deleteAll() {
        SQLiteDatabase db = this.getReadableDatabase();
        String where = "1 = 1";
        String[] whereValue = null;
        db.delete(TABLE_NAME, where, whereValue);
    }
}
